﻿using Dapper;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Model.Ent;
using OMG.Fill.Vegetables.Service;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Vegetable.Service
{
    /// <summary>
    /// 填报基本信息T_Report通用操作类
    /// </summary>
    public class ReportServices:Reports
    {
        public Pager<List<ReportModel>> GetPageList(ReportQuery query)
        {
            AreaServices areaService = new AreaServices();
            string fillYear = query.FillInYear.ToString().Trim();
            DictServices dict = new DictServices();
            query.FillInYear = dict.GetKey("ReportYear", query.FillInYear);
            Pager<List<ReportModel>> obj = new Pager<List<ReportModel>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.FillInYear))
            {
                strWhereSql += "and FillInYear = @FillInYear ";
                paramlist.Add("FillInYear", query.FillInYear);
            }
            if (!string.IsNullOrEmpty(query.TimesType))
            {
                strWhereSql += "and TimesType = @TimesType ";
                paramlist.Add("TimesType", query.TimesType);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.ReportCode))
            {
                strWhereSql += "and ReportCode = @ReportCode ";
                paramlist.Add("ReportCode", query.ReportCode);
            }
            if(!string.IsNullOrEmpty(query.TimesCycle))
            {
                strWhereSql += "and TimesCycle = @TimesCycle ";
                paramlist.Add("TimesCycle", query.TimesCycle);
            }
            //前端默认设置
            //if (!string.IsNullOrEmpty(query.AreaCode))
            //{
            //    strWhereSql += "and AreaCode = @AreaCode ";
            //    paramlist.Add("AreaCode", query.AreaCode);
            //}
            if (!string.IsNullOrEmpty(query.AreaCode))
            {

                if (query.AreaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 2) + "%");
                }
                else if (query.AreaCode.Substring(4, 8).Equals("00000000"))//州级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 4) + "%");
                }
                else
                {
                    strWhereSql += "and AreaCode = @AreaCode ";
                    paramlist.Add("AreaCode", query.AreaCode);
                }
            }
            if (!string.IsNullOrEmpty(query.Status))
            {
                strWhereSql += "and Status = @Status ";
                paramlist.Add("Status", query.Status);
            }
            string strFromSql = "from dbo.T_Report where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = " order by AreaCode asc ";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<ReportModel>(strSql,paramlist).ToList();
                    foreach (var item in obj.rows)
                    {
                        item.StatusName = string.IsNullOrEmpty(item.Status) ? "" : DataDict.Instance["ReportStatus"][item.Status];
                        item.FillInYearName = string.IsNullOrEmpty(item.FillInYear) ? "" : fillYear;
                        item.ReportCodeName = string.IsNullOrEmpty(item.ReportCode) ? "" : DataDict.Instance["ReportTableName"][item.ReportCode];
                        item.TimesTypeName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["ReportCycle"][item.TimesType];
                        item.AreaCodeName = string.IsNullOrEmpty(item.AreaCode) ? "" : areaService.GetByCodeModel(item.AreaCode).model.Name;
                        if (item.TimesType=="1")
                        {
                            if (item.ReportCode == "VegProSurveyYear")
                            {
                                item.ReportCodeName = item.FillInYearName + "-" + item.AreaCodeName + "-(四)主要蔬菜播种面积、产量年度调查表";
                            }
                            else
                            {
                                item.ReportCodeName = item.FillInYearName + "-" + item.AreaCodeName + "-" + item.ReportCodeName;
                            }
                        }
                        if (item.TimesType == "4")
                        {
                            if (item.ReportCode == "VegProSurvey")
                            {
                                item.ReportCodeName = item.FillInYearName + "-" + item.TimesCycle + "-" + item.AreaCodeName + "-(三)主要蔬菜生产情况季度调查表";
                            }
                            else
                            {
                                item.ReportCodeName = item.FillInYearName + "-" + item.TimesCycle + "-" + item.AreaCodeName + "-" + item.ReportCodeName;
                            }
                        }
                        if (item.TimesType == "12")
                        {
                            item.ReportCodeName = item.FillInYearName + "-" + item.TimesCycle + "-" + item.AreaCodeName + "-" + item.ReportCodeName;
                        }
                        item.StrAddTime = item.AddTime == null ? "" : item.AddTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        item.StrUpdateTime = item.UpdateTime == null ? "" : item.UpdateTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        item.Opinion = item.Opinion;
                    }
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取填报基本信息T_Report分页列表失败", ex);
                return obj;
            }
        }



        public ReportDataModel GetReportData(string reportId, string reportCode)
        {
            ReportDataModel model = new ReportDataModel();
            switch (reportCode)
            {
                //蔬菜基本情况调查表
                case "VegBasicInfo":
                    VegBasicInfoServices vegBasicInfoServices = new VegBasicInfoServices();
                    model.ReportId = reportId;
                    model.list = vegBasicInfoServices.GetModel(reportId);
                    break;
                //蔬菜设施情况表
                case "VegFacility":
                    VegFacilityServices vegFacilityServices = new VegFacilityServices();
                    model.ReportId = reportId;
                    model.list = vegFacilityServices.GetModel(reportId);
                    break;
                //蔬菜技术推广机构及人员情况统计表
                case "VegTecPromotion":
                    VegTecPromotionServices vegTecPromotionServices = new VegTecPromotionServices();
                    model.ReportId = reportId;
                    model.list = vegTecPromotionServices.GetModel(reportId);
                    break;
                //蔬菜外销情况调查表
                case "VegExport":
                    VegExportServices vegExportServices = new VegExportServices();
                    model.ReportId = reportId;
                    model.list = vegExportServices.GetModel(reportId);
                    break;
                //蔬菜生产经营实体情况表
                case "VegProsecution":
                    VegProsecutionServices vegProsecutionServices = new VegProsecutionServices();
                    model.ReportId = reportId;
                    model.list = vegProsecutionServices.GetModel(reportId);
                    break;
                //主要蔬菜生产情况季度调查表
                case "VegProSurvey":
                    VegProSurveyServices vegProSurveyServices = new VegProSurveyServices();
                    model.ReportId = reportId;
                    model.list = vegProSurveyServices.GetInfo(reportId);
                    break;
                //主要蔬菜生产情况年度调查表
                case "VegProSurveyYear":
                    VegProSurveyServices vegProSurveyServicesYear = new VegProSurveyServices();
                    model.ReportId = reportId;
                    model.list = vegProSurveyServicesYear.GetInfo(reportId);
                    break;
                //蔬菜育苗专业化组织及生产能力调查表
                case "GrowSeedle":
                    VegGrowSeedleServices vegGrowSeedleServices = new VegGrowSeedleServices();
                    model.list = vegGrowSeedleServices.GetList(reportId,false);
                    break;
                //蔬菜冷库情况调查表
                case "ColdStorage":
                    VegColdStorageServices vegColdStorageServices = new VegColdStorageServices();
                    model.list = vegColdStorageServices.GetList(reportId,false);
                    break;
            }
            model.ReportId = reportId;
            return model;
        }
        /// <summary>
        /// 数据上报
        /// </summary>
        public virtual ResResult<int> UpdateInfo(string Id)
        {
            ResResult<int> obj = new ResResult<int>();
            var paramlist = new DynamicParameters();
            if (string.IsNullOrEmpty(Id))
            {
                paramlist.Add("Id", Id);
                paramlist.Add("Status", "1");
                paramlist.Add("UpdateTime",DateTime.Now);
                string strSql = @"UPDATE dbo.T_Report set ";
                strSql += "Status = @Status,";
                strSql += "UpdateTime = @UpdateTime,";
                strSql += " where Id = @Id";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        int result = dbCon.Query<int>(strSql, paramlist).FirstOrDefault();
                        if (result > 0)
                        {
                            obj.model = result;
                            obj.status = DefaultKey.SuccessResult;
                            obj.message = "更新成功！";
                        }
                        else
                        {
                            obj.model = result;
                            obj.status = DefaultKey.InfoResult;
                            obj.message = "更新失败！数量为:" + result;
                        }
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新填报基本信息T_Report对象信息失败", ex);
                }
            }
            else
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = "更新失败！当前上报对象为空！";
            }
            return obj;
        }
        public ResResult<int> UpStatus(string id, string Status,string Opinion)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_Report set ";
            strSql += "Status = @Status, ";
            strSql += "Opinion=@Opinion ";//审核意见,待定
            strSql += " where Id = @Id";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, new { Id = id, Opinion = Opinion, Status = Status });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "数据报表审核成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "数据报表审核失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新填报基本信息T_Report对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度判断是否生成过报表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表没有生成过", ex);
                return false;
            }
        }

        public ResResult<int> GetExcel(string ReportCode)
        {
            ResResult<int> result = new ResResult<int>();
            string filePath = "";
            if (string.IsNullOrEmpty(ReportCode))
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "报表代码为空,程序有误,请与管理员联系";
            }
            else
            {
                try
                {
                    switch (ReportCode)
                    {
                        //蔬菜基本情况调查表
                        //case "VegBasicInfo":
                        //    VegBasicInfoServices vegBasicInfoServices = new VegBasicInfoServices();
                        //    model.ReportId = reportId;
                        //    model.list = vegBasicInfoServices.(reportId);
                        //    break;
                        ////蔬菜设施情况表
                        //case "VegFacility":
                        //    VegFacilityServices vegFacilityServices = new VegFacilityServices();
                        //    model.ReportId = reportId;
                        //    model.list = vegFacilityServices.GetModel(reportId);
                        //    break;
                        ////蔬菜技术推广机构及人员情况统计表
                        //case "VegTecPromotion":
                        //    VegTecPromotionServices vegTecPromotionServices = new VegTecPromotionServices();
                        //    model.ReportId = reportId;
                        //    model.list = vegTecPromotionServices.GetInfo(reportId);
                        //    break;
                        //蔬菜外销情况调查表
                        case "VegExport":
                            List<reportExcelModel> list = new List<reportExcelModel>();
                            string strSql = @"select SortName,ItemName,Unit,Quantity from V_VegExport order by SortCode asc,ItemCode asc";
                            try
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    list = dbCon.Query<reportExcelModel>(strSql).ToList();
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                            catch (Exception ex)
                            {
                                LogHelper.ErrorLog("更新表2蔬菜外销情况调查表T_VegProSurvey对象信息失败", ex);
                            }
                            filePath = DataExportExcel.RenderToExcel(list, ReportCode);
                            break;
                            ////蔬菜生产经营实体情况表
                            //case "VegProsecution":
                            //    VegProsecutionServices vegProsecutionServices = new VegProsecutionServices();
                            //    model.ReportId = reportId;
                            //    model.list = vegProsecutionServices.GetInfo(reportId);
                            //    break;
                            ////主要蔬菜生产情况调查表
                            //case "VegProSurvey":
                            //    VegProSurveyServices vegProSurveyServices = new VegProSurveyServices();
                            //    model.ReportId = reportId;
                            //    model.list = vegProSurveyServices.GetInfo(reportId);
                            //    break;
                    }
                    result.message = "模板下载成功,文件保存路径:" + filePath;
                    result.status = DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    result.message = "程序异常,报表导出失败,请与管理员联系！" + ex.ToString().Trim();
                    result.status = DefaultKey.ErrorResult;
                    LogHelper.WriteLog(ex.ToString().Trim());
                }
            }
            return result;
        }
        /// <summary>
        /// 获取报表任务完成情况
        /// </summary>
        /// <param name="AreaCode">地区代码</param>
        /// <returns></returns>
        public ResResult<List<ReportReview>> getReportReview(string AreaCode,string FillInYear)
        {
            ResResult<List<ReportReview>> obj = new ResResult<List<ReportReview>>();
            DictServices dict = new DictServices();
            FillInYear = dict.GetKey("ReportYear",FillInYear);
            var paramlist = new DynamicParameters();
            string strSql = "select ReportName,sort,ISNULL(a.[0], 0) as 'ReviewFalse' ,";
            strSql += "ISNULL(a.[1], 0) as 'NotFillIn' ,ISNULL(a.[2], 0) as 'FillIn' ,";
            strSql += "ISNULL(a.[3], 0) as 'AlredayReport' ,ISNULL(a.[4], 0) as 'ReviewSuccess' ,";
            strSql += "ISNULL(a.[5], 0) as 'Lock'from(";
            strSql += "select reportName,sort,Status, COUNT(1) RS from[T_Report]";
            paramlist.Add("FillInyear", FillInYear);
            if (!string.IsNullOrEmpty(AreaCode)&& AreaCode!= "null")
            {
                //paramlist.Add("AreaCode",AreaCode);
                //strSql += "where AreaCode = @AreaCode";
                if (AreaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                {
                    strSql += "where AreaCode like @AreaCode  and FillInYear = @FillInYear ";
                    paramlist.Add("AreaCode", "%" + AreaCode.Substring(0, 2) + "%");
                }
                else if (AreaCode.Substring(4, 8).Equals("00000000"))//州级账号
                {
                    strSql += "where AreaCode like @AreaCode   and FillInYear = @FillInYear ";
                    paramlist.Add("AreaCode", "%" + AreaCode.Substring(0, 4) + "%");
                }
                else
                {
                    strSql += "where AreaCode = @AreaCode  and FillInYear = @FillInYear  ";
                    paramlist.Add("AreaCode", AreaCode);
                }
            }
            else
            {
                strSql += "where  FillInYear = @FillInYear  ";
            }
            strSql += "  group by reportName,[Status],sort ";
            strSql += " ) t";
            strSql += " pivot(max(t.RS) FOR t.[Status] IN ([0] , [1], [2], [3], [4], [5])) a order by sort ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<ReportReview>(strSql, paramlist).ToList(); 
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "任务完成结果获取成功";

                }
            }
            catch(Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = "任务完成结果获取失败"+ex.ToString().Trim();
            }
            dbCon.Dispose();//数据库连接释放
            return obj;
        }

        /// <summary>
        /// 获取本年和上一年的数据
        /// </summary>
        /// <param name="nowModel"></param>
        /// <returns></returns>
        public ReportDataModel GetModel(VegNowModel nowModel) {
            ReportDataModel model = new ReportDataModel();
            ReportServices reportServices = new ReportServices();
            DictServices dictServices = new DictServices();
            nowModel.FillInYear= dictServices.GetKey("ReportYear", nowModel.FillInYear);
            switch (nowModel.ReportCode) {
                //蔬菜基本情况调查表
                case "VegBasicInfo":
                    VegBasicInfoServices vegBasicInfoServices = new VegBasicInfoServices();
                    model.list = vegBasicInfoServices.GetModel(nowModel);
                    break;
                //蔬菜设施情况表
                case "VegFacility":
                    VegFacilityServices vegFacilityServices = new VegFacilityServices();
                    model.list = vegFacilityServices.GetModel(nowModel);
                    break;
                //蔬菜技术推广机构及人员情况统计表
                case "VegTecPromotion":
                    VegTecPromotionServices vegTecPromotionServices = new VegTecPromotionServices();
                    model.list = vegTecPromotionServices.GetModel(nowModel);
                    break;
                //蔬菜外销情况调查表
                case "VegExport":
                    VegExportServices vegExportServices = new VegExportServices();
                    model.list = vegExportServices.GetModel(nowModel);
                    break;
                //蔬菜生产经营实体情况表
                case "VegProsecution":
                    VegProsecutionServices vegProsecutionServices = new VegProsecutionServices();
                    model.list = vegProsecutionServices.GetModel(nowModel);
                    break;
                //蔬菜育苗专业化组织及生产能力调查表
                case "GrowSeedle":
                    VegGrowSeedleServices vegGrowSeedleServices = new VegGrowSeedleServices();
                    model.list = vegGrowSeedleServices.GetList(reportServices.GetReportId(nowModel.ReportCode, nowModel.AreaCode, nowModel.FillInYear),true);
                    break;
                //蔬菜冷库情况调查表
                case "ColdStorage":
                    VegColdStorageServices vegColdStorageServices = new VegColdStorageServices();
                    model.list = vegColdStorageServices.GetList(reportServices.GetReportId(nowModel.ReportCode, nowModel.AreaCode, nowModel.FillInYear), true);
                    break;

            }
            return model;
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度获取报表Id
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表没有生成过", ex);
                return "0";
            }
        }
    }
}